﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using QuanLyDoanVien.General;
using QuanLyDoanVien.Bussiness;
using System.Data.SqlClient;
using System.Data;

namespace QuanLyDoanVien.DataLayer
{
    class HoatDongHocSinhData
    {
        DataService m_HoatDongHocSinhData = new DataService();

        public List<HoatDongHocSinhInfo> layDSHoatDongHS()
        {
            List<HoatDongHocSinhInfo> rs = new List<HoatDongHocSinhInfo>();
            DataService.OpenConnection();
            string sql = "select * from Tab_HoatDongHocSinh";
            SqlCommand command = new SqlCommand(sql);
            m_HoatDongHocSinhData.Load(command);
            foreach (DataRow dr in m_HoatDongHocSinhData.Rows)
            {
                HoatDongHocSinhInfo hdhs = new HoatDongHocSinhInfo();
                try
                {
                    hdhs.MaHoatDong = dr["MaHoatDong"].ToString();
                    hdhs.MaHocSinh = dr["MaHocSinh"].ToString();
                    hdhs.MucDo = dr["MucDo"].ToString();
                    hdhs.PhanCong = int.Parse(dr["PhanCong"].ToString());
                    hdhs.ThamGia = int.Parse(dr["ThamGia"].ToString());
                    hdhs.GhiChu = dr["GhiChu"].ToString();
                    rs.Add(hdhs);
                }
                catch
                {
                }
            }
            m_HoatDongHocSinhData.CloseConnection();
            return rs;
        }

        public List<HoatDongHocSinhInfo> layDSHDHS(string maHocSinh)
        {
            DataService.OpenConnection();
            List<HoatDongHocSinhInfo>rs = new List<HoatDongHocSinhInfo>();
            string sql = "select * from Tab_HoatDongHocSinh where MaHocSinh=@MaHocSinh";
            SqlCommand command = new SqlCommand(sql);
            command.Parameters.Add("MaHocSinh", SqlDbType.Char).Value = maHocSinh;
            m_HoatDongHocSinhData.Load(command);
            foreach (DataRow dr in m_HoatDongHocSinhData.Rows)
            {
               HoatDongHocSinhInfo hdhs = new HoatDongHocSinhInfo();
                try
                {
                    hdhs.MaHoatDong = dr["MaHoatDong"].ToString();
                    hdhs.MaHocSinh = dr["MaHocSinh"].ToString();
                    hdhs.MucDo = dr["MucDo"].ToString();
                    hdhs.PhanCong = int.Parse(dr["PhanCong"].ToString());
                    hdhs.ThamGia = int.Parse(dr["ThamGia"].ToString());
                    hdhs.GhiChu = dr["GhiChu"].ToString();

                    rs.Add(hdhs);
                }
                catch
                {
                }
             
            }  
           
            m_HoatDongHocSinhData.CloseConnection();
            return rs;
           
        }

        public HoatDongHocSinhInfo layHDHS(string maHD, string maHocSinh)
        {
            DataService.OpenConnection();
            HoatDongHocSinhInfo hdhs = null;
            string sql = "select * from Tab_HoatDongHocSinh where MaHoatDong=@MaHoatDong and MaHocSinh=@MaHocSinh";
            SqlCommand command = new SqlCommand(sql);
            command.Parameters.Add("MaHoatDong", SqlDbType.Char).Value = maHD;
            command.Parameters.Add("MaHocSinh", SqlDbType.Char).Value = maHocSinh;
            m_HoatDongHocSinhData.Load(command);
            foreach (DataRow dr in m_HoatDongHocSinhData.Rows)
            {
                hdhs = new HoatDongHocSinhInfo();
                try
                {
                    hdhs.MaHoatDong = dr["MaHoatDong"].ToString();
                    hdhs.MaHocSinh = dr["MaHocSinh"].ToString();
                    hdhs.MucDo = dr["MucDo"].ToString();
                    hdhs.PhanCong = int.Parse(dr["PhanCong"].ToString());
                    hdhs.ThamGia = int.Parse(dr["ThamGia"].ToString());
                    hdhs.GhiChu = dr["GhiChu"].ToString();

                   
                }
                catch
                {
                }
                return hdhs;
            }
            m_HoatDongHocSinhData.CloseConnection();
            return null;
        }
       
        public bool themHoatDongHS(HoatDongHocSinhInfo hdhs)
        {
            DataService.OpenConnection();
            string sql = "insert into Tab_HoatDongHocSinh(MaHoatDong,MaHocSinh,MucDo,PhanCong,ThamGia,GhiChu)";
            sql += " Values(@MaHoatDong,@MaHocSinh,@MucDo,@PhanCong,@ThamGia,@GhiChu)";
            SqlCommand command = new SqlCommand(sql);
            command.Parameters.Add("MaHoatDong", SqlDbType.Char).Value = hdhs.MaHoatDong;
            command.Parameters.Add("MaHocSinh", SqlDbType.Char).Value = hdhs.MaHocSinh;
            command.Parameters.Add("MucDo", SqlDbType.Char).Value = hdhs.MucDo;
            command.Parameters.Add("PhanCong", SqlDbType.Int).Value = hdhs.PhanCong;
            command.Parameters.Add("ThamGia", SqlDbType.Int).Value = hdhs.ThamGia;
            command.Parameters.Add("GhiChu", SqlDbType.NVarChar).Value = hdhs.GhiChu;
            try
            {
                m_HoatDongHocSinhData.ExecuteNoneQuery(command);
                m_HoatDongHocSinhData.CloseConnection();
                return true;
            }
            catch(Exception ex)
            {
                m_HoatDongHocSinhData.CloseConnection();
                return false;
            }
        }

        public bool xoaHoatDongHS(string maHD, string maHS)
        {
            DataService.OpenConnection();
            string sql = "Delete from Tab_HoatDongHocSinh where MaHoatDong=@MaHoatDong and MaHocSinh=@MaHocSinh";
            SqlCommand command = new SqlCommand(sql);
            command.Parameters.Add("MaHoatDong", SqlDbType.Char).Value = maHD;
            command.Parameters.Add("MaHocSinh", SqlDbType.Char).Value = maHS;
            try
            {
                m_HoatDongHocSinhData.ExecuteNoneQuery(command);
                m_HoatDongHocSinhData.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                m_HoatDongHocSinhData.CloseConnection();
                return false;
            }
          
        }

        public bool capNhatHoatDongHS(HoatDongHocSinhInfo hdhs)
        {
            DataService.OpenConnection();
            string sql = "Update Tab_HoatDongHocSinh set MucDo=@MucDo,PhanCong=@PhanCong,ThamGia=@ThamGia,GhiChu=@GhiChu where MaHoatDong=@MaHoatDong and MaHocSinh=@MaHocSinh";
            SqlCommand command = new SqlCommand(sql);
            command.Parameters.Add("MaHoatDong", SqlDbType.Char).Value = hdhs.MaHoatDong;
            command.Parameters.Add("MaHocSinh", SqlDbType.Char).Value = hdhs.MaHocSinh;
            command.Parameters.Add("MucDo", SqlDbType.Char).Value = hdhs.MucDo;
            command.Parameters.Add("PhanCong", SqlDbType.Int).Value = hdhs.PhanCong;
            command.Parameters.Add("ThamGia", SqlDbType.Int).Value = hdhs.ThamGia;
            command.Parameters.Add("GhiChu", SqlDbType.NVarChar).Value = hdhs.GhiChu;
            try
            {
                m_HoatDongHocSinhData.ExecuteNoneQuery(command);
                m_HoatDongHocSinhData.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                m_HoatDongHocSinhData.CloseConnection();
                return false;
            }
        }
        public bool capNhatHoatDongHS(string maHS,string maHD,string diem)
        {
            DataService.OpenConnection();
            string sql = "Update Tab_HoatDongHocSinh set MucDo=@MucDo where MaHocSinh=@MaHocSinh and MaHoatDong=@MaHoatDong";
            SqlCommand command = new SqlCommand(sql);
            command.Parameters.Add("MaHoatDong", SqlDbType.Char).Value = maHD;
            command.Parameters.Add("MaHocSinh", SqlDbType.Char).Value = maHS;
            command.Parameters.Add("MucDo", SqlDbType.Char).Value = diem;
            
            try
            {
                m_HoatDongHocSinhData.ExecuteNoneQuery(command);
                m_HoatDongHocSinhData.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                m_HoatDongHocSinhData.CloseConnection();
                return false;
            }
        }
        public List<HoatDongHocSinhInfo> layDSHD(string maHD, int thamGia)
        {
            List<HoatDongHocSinhInfo> rs = new List<HoatDongHocSinhInfo>();
            DataService.OpenConnection();
            string sql = "select * from Tab_HoatDongHocSinh where MaHoatDong=@MaHoatDong and ThamGia=@ThamGia";
            SqlCommand command = new SqlCommand(sql);
            command.Parameters.Add("MaHoatDong", SqlDbType.Char).Value = maHD;
            command.Parameters.Add("ThamGia", SqlDbType.Int).Value = thamGia;
            m_HoatDongHocSinhData.Load(command);
            foreach (DataRow dr in m_HoatDongHocSinhData.Rows)
            {
                HoatDongHocSinhInfo hdhs = new HoatDongHocSinhInfo();
                try
                {
                    hdhs.MaHoatDong = dr["MaHoatDong"].ToString();
                    hdhs.MaHocSinh = dr["MaHocSinh"].ToString();
                    hdhs.MucDo = dr["MucDo"].ToString();
                    hdhs.PhanCong = int.Parse(dr["PhanCong"].ToString());
                    hdhs.ThamGia = int.Parse(dr["ThamGia"].ToString());
                    hdhs.GhiChu = dr["GhiChu"].ToString();
                    rs.Add(hdhs);
                }
                catch
                {
                }
            }
            m_HoatDongHocSinhData.CloseConnection();
            return rs;
        }

        public bool capNhatThamGia(string maHD, string maHS, int thamGia)
        {
            DataService.OpenConnection();
            string sql = "Update Tab_HoatDongHocSinh set ThamGia=@ThamGia where MaHoatDong=@MaHoatDong and MaHocSinh=@MaHocSinh";
            SqlCommand command = new SqlCommand(sql);
            command.Parameters.Add("MaHoatDong", SqlDbType.Char).Value =maHD;
            command.Parameters.Add("MaHocSinh", SqlDbType.Char).Value = maHS;
        
            command.Parameters.Add("ThamGia", SqlDbType.Int).Value = thamGia;
            
            try
            {
                m_HoatDongHocSinhData.ExecuteNoneQuery(command);
                m_HoatDongHocSinhData.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                m_HoatDongHocSinhData.CloseConnection();
                return false;
            }
        }

        public DataTable layHoatDongHocSinh1(string maHD)
        {
            DataService.OpenConnection();
            string sql = "select hs.MaHocSinh, hs.HoTen,l.TenLop";
            sql += " from (Tab_HocSinh hs inner join Tab_HoatDongHocSinh hdhs on hs.MaHocSinh=hdhs.MaHocSinh)";
            sql += " inner join Tab_Lop l on hs.MaLop=l.MaLop";
            sql += " where hdhs.MaHoatDong=@MaHoatDong and hdhs.PhanCong=1";
            SqlCommand command = new SqlCommand(sql);
            command.Parameters.Add("MaHoatDong", SqlDbType.Char).Value = maHD;
            m_HoatDongHocSinhData.Load(command);
            m_HoatDongHocSinhData.CloseConnection();
            return m_HoatDongHocSinhData;
        }

        public DataTable layDSHDHocSinh(string maLop)
        {
          
            DataService.OpenConnection();
            string sql = "select hs.MaHocSinh, hs.HoTen,hd.TenHoatDong,l.TenLop,hdhs.MucDo,hdhs.GhiChu";
            sql += " from ((Tab_HocSinh hs inner join Tab_HoatDongHocSinh hdhs on hs.MaHocSinh=hdhs.MaHocSinh) ";
            sql += " inner join Tab_HoatDong hd on hd.MaHoatDong=hdhs.MaHoatDong ) inner join Tab_Lop l on l.MaLop = hs.MaLop";
            sql += "  where l.MaLop='"+maLop+"'";
            SqlCommand command = new SqlCommand(sql);
            m_HoatDongHocSinhData.Load(command);
            m_HoatDongHocSinhData.CloseConnection();
            return m_HoatDongHocSinhData;
        }

        public DataTable layHDHS(string maHocSinh)
        {
            DataService.OpenConnection();
            string sql = "select hs.MaHocSinh, hs.HoTen,hd.TenHoatDong,l.TenLop,hdhs.MucDo,hdhs.GhiChu";
            sql += " from ((Tab_HocSinh hs inner join Tab_HoatDongHocSinh hdhs on hs.MaHocSinh=hdhs.MaHocSinh) ";
            sql += " inner join Tab_HoatDong hd on hd.MaHoatDong=hdhs.MaHoatDong ) inner join Tab_Lop l on l.MaLop = hs.MaLop";
            sql += "  where hs.MaHocSinh='" + maHocSinh + "'";
            SqlCommand command = new SqlCommand(sql);
            m_HoatDongHocSinhData.Load(command);
            m_HoatDongHocSinhData.CloseConnection();
            return m_HoatDongHocSinhData;
        }
    }
}
